<?php

namespace SLN0001\DAO;

use Home\Common\FIdConst;
use Home\DAO\BizConfigDAO;
use Home\DAO\DataOrgDAO;
use Home\DAO\PSIBaseExDAO;

/**
 * 增值税发票 DAO
 *
 * @author PSI
 * @copyright 2015 - present
 * @license GPL v3
 */
class VatDAO extends PSIBaseExDAO
{
  private function categoryCodeToName($code)
  {
    $db = $this->db;

    $sql = "select name
            from t_sysdict_sln0001_vat_category
            where code = '%s' ";
    $data = $db->query($sql, $code);
    if (!$data) {
      return "[无效的编码]";
    }

    return $data[0]["name"];
  }

  /**
   * 增值税进项发票列表
   */
  public function incomeList($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $status = $params["status"];
    $ref = $params["ref"];
    $fromDT = $params["fromDT"];
    $toDT = $params["toDT"];
    $supplierId = $params["supplierId"];
    $category = $params["category"];
    $start = $params["start"];
    $limit = $params["limit"];

    $sql = "select v.id, v.ref, v.v_dt, v_status, v_category,
              v_dt, concat(s.code, ' - ', s.name) as supplier_name, s.id as supplier_id,
              v.goods_money, v.tax, v.goods_money_with_tax,
              u.name as input_user_name, v.input_dt, v.memo, v.v_file_update_dt 
            from t_vat_income v, t_supplier s, t_user u
            where v.supplier_id = s.id and v.input_user_id = u.id
              and v.company_id = '%s' ";
    $queryParams = [];
    $queryParams[] = $companyId;
    if ($status != -1) {
      $sql .= " and v.v_status = %d";
      $queryParams[] = $status;
    }
    if ($ref) {
      $sql .= " and v.ref like '%s' ";
      $queryParams[] = "%{$ref}%";
    }
    if ($fromDT) {
      $sql .= " and v.v_dt >= '%s' ";
      $queryParams[] = $fromDT;
    }
    if ($toDT) {
      $sql .= " and v.v_dt <= '%s' ";
      $queryParams[] = $toDT;
    }
    if ($supplierId) {
      $sql .= " and v.supplier_id = '%s' ";
      $queryParams[] = $supplierId;
    }
    if ($category != -1) {
      $sql .= " and v.v_category = %d";
      $queryParams[] = $category;
    }
    $sql .= " order by v.v_dt desc, v.ref
            limit %d, %d";
    $queryParams[] = $start;
    $queryParams[] = $limit;
    $data = $db->query($sql, $queryParams);

    $result = [];
    foreach ($data as $v) {
      $result[] = [
        "id" => $v["id"],
        "ref" => $v["ref"],
        "status" => $v["v_status"],
        "category" => $this->categoryCodeToName($v["v_category"]),
        "dt" => $v["v_dt"],
        "supplierId" => $v["supplier_id"],
        "supplierName" => $v["supplier_name"],
        "goodsMoney" => $v["goods_money"],
        "tax" => $v["tax"],
        "moneyWithTax" => $v["goods_money_with_tax"],
        "inputUserName" => $v["input_user_name"],
        "inputDT" => $v["input_dt"],
        "memo" => $v["memo"],
        "file" => $v["v_file_update_dt"] ? "√" : null,
        "fileDT" => $v["v_file_update_dt"],
      ];
    }


    $sql = "select count(*) as cnt
            from t_vat_income
            where company_id = '%s'";
    $queryParams = [];
    $queryParams[] = $companyId;
    if ($status != -1) {
      $sql .= " and v_status = %d";
      $queryParams[] = $status;
    }
    if ($ref) {
      $sql .= " and ref like '%s' ";
      $queryParams[] = "%{$ref}%";
    }
    if ($fromDT) {
      $sql .= " and v_dt >= '%s' ";
      $queryParams[] = $fromDT;
    }
    if ($toDT) {
      $sql .= " and v_dt <= '%s' ";
      $queryParams[] = $toDT;
    }
    if ($supplierId) {
      $sql .= " and supplier_id = '%s' ";
      $queryParams[] = $supplierId;
    }
    if ($category != -1) {
      $sql .= " and v_category = %d";
      $queryParams[] = $category;
    }
    $data = $db->query($sql, $queryParams);
    $cnt = $data[0]["cnt"];

    return [
      "dataList" => $result,
      "totalCount" => $cnt,
    ];
  }

  /**
   * 新建进项发票记录
   */
  public function addIncomeBill(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $ref = $params["ref"];
    $status = intval($params["status"]);
    $category = intval($params["category"]);
    $dt = $params["dt"];
    $supplierId = $params["supplierId"];
    $goodsMoney = floatval($params["goodsMoney"]);
    $tax = floatval($params["tax"]);
    $memo = $params["memo"];
    if (!$memo) {
      $memo = "";
    }

    $loginUserId = $params["loginUserId"];
    $dataOrg = $params["dataOrg"];

    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    // 检查该发票号是否已经存在
    $sql = "select count(*) as cnt from t_vat_income
            where company_id = '%s' and ref = '%s' ";
    $data = $db->query($sql, $companyId, $ref);
    $cnt = $data[0]["cnt"];
    if ($cnt > 0) {
      return $this->bad("发票号为{$ref}的发票记录已经存在，不能重复新建发票记录");
    }

    if (!in_array($status, [0, 1000])) {
      return $this->badParam("status");
    }
    if (!in_array($category, [1, 2, 3])) {
      return $this->badParam("category");
    }

    // 检查供应商
    $sql = "select name
            from t_supplier
            where id = '%s' ";
    $data = $db->query($sql, $supplierId);
    if (!$data) {
      return $this->badParam("supplierId");
    }

    $id = $this->newId();

    $sql = "insert into t_vat_income
            (id, v_status, v_dt, v_category, ref, supplier_id, 
            goods_money, tax, goods_money_with_tax, input_user_id,
            input_dt, company_id, data_org, memo)
            values 
            ('%s', %d, '%s', %d, '%s', '%s',
            %f, %f, %f, '%s',
            now(), '%s', '%s', '%s')";
    $rc = $db->execute(
      $sql,
      $id,
      $status,
      $dt,
      $category,
      $ref,
      $supplierId,
      $goodsMoney,
      $tax,
      $goodsMoney + $tax,
      $loginUserId,
      $companyId,
      $dataOrg,
      $memo
    );
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "{$companyName} - 新建发票号为{$ref}的发票记录";
    $params["log"] = $log;

    $params["id"] = $id;

    // 操作成功
    return null;
  }

  /**
   * 编辑进项发票记录
   */
  public function updateIncomeBill(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $id = $params["id"];
    $sql = "select v_status
            from t_vat_income
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要编辑的进项发票记录不存在");
    }
    $v = $data[0];
    $currentStatus = $v["v_status"];
    if ($currentStatus > 1000) {
      return $this->bad("进项发票记录已经核销，不能再编辑");
    }

    $ref = $params["ref"];
    $status = intval($params["status"]);
    $category = intval($params["category"]);
    $dt = $params["dt"];
    $supplierId = $params["supplierId"];
    $goodsMoney = floatval($params["goodsMoney"]);
    $tax = floatval($params["tax"]);
    $memo = $params["memo"];
    if (!$memo) {
      $memo = "";
    }

    if (!in_array($status, [0, 1000])) {
      return $this->bad("状态不能手工修改为已核销");
    }

    // 检查发票号是否唯一
    $sql = "select count(*) as cnt
            from t_vat_income
            where company_id = '%s' and ref = '%s' and id <> '%s' ";
    $data = $db->query($sql, $companyId, $ref, $id);
    $cnt = $data[0]["cnt"];
    if ($cnt > 0) {
      return $this->bad("发票号为{$ref}的发票记录以及存在");
    }

    // 检查供应商是否存在
    $sql = "select name from t_supplier where id = '%s' ";
    $data = $db->query($sql, $supplierId);
    if (!$data) {
      // bug
      return $this->badParam("supplierId");
    }

    $sql = "update t_vat_income
            set ref = '%s', v_status = %d, v_category = %d,
                v_dt = '%s', supplier_id = '%s', goods_money = %f,
                tax = %f, goods_money_with_tax = %f, memo = '%s'
            where id = '%s' ";
    $rc = $db->execute(
      $sql,
      $ref,
      $status,
      $category,
      $dt,
      $supplierId,
      $goodsMoney,
      $tax,
      $goodsMoney + $tax,
      $memo,
      $id
    );
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    $log = "{$companyName} - 编辑发票号为{$ref}的进项发票记录";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 删除进项发票记录
   */
  public function deleteVatIncome(&$params)
  {
    $db = $this->db;

    $id = $params["id"];

    $sql = "select v.ref, v.v_status, g.name
            from t_vat_income v, t_org g
            where v.company_id = g.id and v.id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要删除的增值税发票进项记录不存在");
    }
    $v = $data[0];
    $status = $v["v_status"];
    $companyName = $v["name"];
    $ref = $v["ref"];

    if ($status > 1000) {
      return $this->bad("发票已经核销，不能再删除记录了");
    }

    $sql = "delete from t_vat_income where id = '%s' ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "{$companyName} - 删除发票号为{$ref}的进项发票记录";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 查询进项发票记录的详情
   */
  public function vatIncomeInfo($params)
  {
    $db = $this->db;

    $id = $params["id"];

    $sql = "select v.v_status, v.ref, v.v_dt, v.v_category,
              v.supplier_id, s.name as supplier_name,
              v.goods_money, v.tax, v.memo
            from t_vat_income v, t_supplier s
            where v.id = '%s' and v.supplier_id = s.id";
    $data = $db->query($sql, $id);
    if (!$data) {
      // 莫名的bug
      return $this->emptyResult();
    }

    $v = $data[0];
    $result = [
      "ref" => $v["ref"],
      "status" => $v["v_status"],
      "dt" => $v["v_dt"],
      "category" => $v["v_category"],
      "supplierId" => $v["supplier_id"],
      "supplierName" => $v["supplier_name"],
      "goodsMoney" => $v["goods_money"],
      "tax" => $v["tax"],
      "memo" => $v["memo"],
    ];

    return $result;
  }

  /**
   * 上传进项电子发票
   */
  public function uploadIncomePDF(&$params)
  {
    $db = $this->db;
    $id = $params["id"];

    $sql = "select ref, company_id
            from t_vat_income
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("进项发票记录不存在");
    }
    $ref = $data[0]["ref"];
    $companyId = $data[0]["company_id"];

    $fileNameWithPath = $params["uploadFilePath"];
    $fileSize = $params["fileSize"];
    // 从磁盘中读取文件
    $fileData = file_get_contents($fileNameWithPath);

    $sql = "update t_vat_income
            set v_file = '%s', v_file_size = %d, v_file_update_dt = now()
            where id = '%s' ;";
    $rc = $db->execute($sql, $fileData, $fileSize, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $sql = "select name from t_org where id = '%s' ";
    $data = $db->query($sql, $companyId);
    $companyName = $data[0]["name"];
    $log = "{$companyName} - 上传电子发票，发票号：{$ref}";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 获得增值税进项发票的电子发票文件
   */
  public function getVatIncomeFile($params)
  {
    $db = $this->db;

    $id = $params["id"];
    $sql = "select company_id, ref, v_file, v_file_size 
            from t_vat_income 
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->badParam("id");
    }

    $companyId = $data[0]["company_id"];
    $ref = $data[0]["ref"];
    $fileData = $data[0]["v_file"];
    $fileSize = $data[0]["v_file_size"];
    if (!$fileData) {
      return $this->bad("还没有上传发票");
    }

    // 业务日志
    $sql = "select name from t_org where id = '%s' ";
    $data = $db->query($sql, $companyId);
    $companyName = $data[0]["name"];
    $log = "{$companyName} - 下载发票号为{$ref}电子发票";

    return [
      "success" => true,
      "fileData" => $fileData,
      "fileSize" => $fileSize,
      "ref" => $ref,
      "log" => $log
    ];
  }

  /**
   * 获得采购入库单主表列表
   *
   * @param array $params
   * @return array
   */
  public function pwbillList($params)
  {
    $db = $this->db;

    $start = $params["start"];
    $limit = $params["limit"];

    // 单号
    $ref = $params["ref"];

    // 供应商id
    $supplierId = $params["supplierId"];

    $loginUserId = $params["loginUserId"];
    if ($this->loginUserIdNotExists($loginUserId)) {
      return $this->emptyResult();
    }

    $queryParams = [];
    $sql = "select p.id, p.bill_status, p.ref, p.biz_dt, u1.name as biz_user_name, u2.name as input_user_name,
              p.goods_money, w.name as warehouse_name, s.name as supplier_name,
              p.date_created, p.payment_type, p.bill_memo, p.expand_by_bom, p.wspbill_id,
              p.tax, p.money_with_tax
            from t_pw_bill p, t_warehouse w, t_supplier s, t_user u1, t_user u2
            where (p.warehouse_id = w.id) and (p.supplier_id = s.id)
            and (p.biz_user_id = u1.id) and (p.input_user_id = u2.id) ";

    $ds = new DataOrgDAO($db);
    // 构建数据域SQL
    $rs = $ds->buildSQL(FIdConst::VAT_IN_REL_ADD, "p", $loginUserId);
    if ($rs) {
      $sql .= " and " . $rs[0];
      $queryParams = $rs[1];
    }

    if ($ref) {
      $sql .= " and (p.ref like '%s') ";
      $queryParams[] = "%{$ref}%";
    }
    if ($supplierId) {
      $sql .= " and (p.supplier_id = '%s') ";
      $queryParams[] = $supplierId;
    }

    $sql .= " order by p.biz_dt desc, p.ref desc
              limit %d, %d";
    $queryParams[] = $start;
    $queryParams[] = $limit;
    $data = $db->query($sql, $queryParams);
    $result = [];

    foreach ($data as $v) {
      $wspBillId = $v["wspbill_id"];
      $wspBillRef = null;
      if ($wspBillId) {
        $sql = "select ref from t_wsp_bill where id = '%s' ";
        $d = $db->query($sql, $wspBillId);
        if ($d) {
          $wspBillRef = $d[0]["ref"];
        }
      }

      $result[] = [
        "id" => $v["id"],
        "ref" => $v["ref"],
        "bizDate" => $this->toYMD($v["biz_dt"]),
        "supplierName" => $v["supplier_name"],
        "warehouseName" => $v["warehouse_name"],
        "inputUserName" => $v["input_user_name"],
        "bizUserName" => $v["biz_user_name"],
        "billStatus" => $this->pwbillStatusCodeToName($v["bill_status"]),
        "billStatusCode" => $v["bill_status"],
        "amount" => $v["goods_money"],
        "dateCreated" => $v["date_created"],
        "paymentType" => $v["payment_type"],
        "billMemo" => $v["bill_memo"],
        "expandByBOM" => $v["expand_by_bom"],
        "wspBillRef" => $wspBillRef,
        "tax" => $v["tax"],
        "moneyWithTax" => $v["money_with_tax"],
      ];
    }

    $sql = "select count(*) as cnt
            from t_pw_bill p, t_warehouse w, t_supplier s, t_user u1, t_user u2
            where (p.warehouse_id = w.id) and (p.supplier_id = s.id)
            and (p.biz_user_id = u1.id) and (p.input_user_id = u2.id)";
    $queryParams = [];
    $ds = new DataOrgDAO($db);
    $rs = $ds->buildSQL(FIdConst::VAT_IN_REL_ADD, "p", $loginUserId);
    if ($rs) {
      $sql .= " and " . $rs[0];
      $queryParams = $rs[1];
    }
    if ($ref) {
      $sql .= " and (p.ref like '%s') ";
      $queryParams[] = "%{$ref}%";
    }
    if ($supplierId) {
      $sql .= " and (p.supplier_id = '%s') ";
      $queryParams[] = $supplierId;
    }

    $data = $db->query($sql, $queryParams);
    $cnt = $data[0]["cnt"];

    return [
      "dataList" => $result,
      "totalCount" => $cnt
    ];
  }

  private function pwbillStatusCodeToName($code)
  {
    switch ($code) {
      case 0:
        return "待入库(在途)";
      case 1000:
        return "已入库";
      case 2000:
        return "部分退货";
      case 3000:
        return "全部退货";
      default:
        return "";
    }
  }

  /**
   * 获得采购入库单物料明细记录列表
   *
   * @param array $params
   * @return array
   */
  public function pwBillDetailList($params)
  {
    $companyId = $params["companyId"];
    if ($this->companyIdNotExists($companyId)) {
      return $this->emptyResult();
    }

    $db = $this->db;

    $bcDAO = new BizConfigDAO($db);
    $dataScale = $bcDAO->getGoodsCountDecNumber($companyId);
    $fmt = "decimal(19, " . $dataScale . ")";

    // 采购入库单id
    $pwbillId = $params["id"];

    $sql = "select p.id, g.code, g.name, g.spec, u.name as unit_name, 
              convert(p.goods_count, $fmt) as goods_count, p.goods_price,
              p.goods_money, p.memo, p.tax_rate, p.tax, p.money_with_tax,
              p.goods_price_with_tax, convert(p.rej_goods_count, $fmt) as rej_goods_count,
              convert(p.real_goods_count, $fmt) as real_goods_count
            from t_pw_bill_detail p, t_goods g, t_goods_unit u
            where p.pwbill_id = '%s' and p.goods_id = g.id and g.unit_id = u.id
            order by p.show_order ";
    $data = $db->query($sql, $pwbillId);
    $result = [];

    foreach ($data as $v) {
      $result[] = [
        "id" => $v["id"],
        "goodsCode" => $v["code"],
        "goodsName" => $v["name"],
        "goodsSpec" => $v["spec"],
        "unitName" => $v["unit_name"],
        "goodsCount" => $v["goods_count"],
        "rejGoodsCount" => $v["rej_goods_count"],
        "realGoodsCount" => $v["real_goods_count"],
        "goodsMoney" => $v["goods_money"],
        "goodsPrice" => $v["goods_price"],
        "memo" => $v["memo"],
        "taxRate" => $v["tax_rate"],
        "tax" => $v["tax"],
        "moneyWithTax" => $v["money_with_tax"],
        "goodsPriceWithTax" => $v["goods_price_with_tax"]
      ];
    }

    return $result;
  }

  /**
   * 采购退货出库单列表
   *
   * @param array $params
   * @return array
   */
  public function prbillList($params)
  {
    $db = $this->db;

    $start = $params["start"];
    $limit = $params["limit"];

    $ref = $params["ref"];
    $supplierId = $params["supplierId"];

    $loginUserId = $params["loginUserId"];
    if ($this->loginUserIdNotExists($loginUserId)) {
      return $this->emptyResult();
    }

    $result = [];
    $queryParams = [];
    $sql = "select p.id, p.ref, p.bill_status, w.name as warehouse_name, p.bizdt,
              p.rejection_money, u1.name as biz_user_name, u2.name as input_user_name,
              s.name as supplier_name, p.date_created, p.receiving_type, p.bill_memo,
              p.tax, p.rejection_money_with_tax, pw.ref as pw_ref
            from t_pr_bill p, t_warehouse w, t_user u1, t_user u2, t_supplier s,
              t_pw_bill pw
            where (p.warehouse_id = w.id)
              and (p.biz_user_id = u1.id)
              and (p.input_user_id = u2.id)
              and (p.supplier_id = s.id) 
              and (p.pw_bill_id = pw.id) ";

    $ds = new DataOrgDAO($db);
    $rs = $ds->buildSQL(FIdConst::VAT_IN_REL_ADD, "p", $loginUserId);
    if ($rs) {
      $sql .= " and " . $rs[0];
      $queryParams = $rs[1];
    }

    if ($ref) {
      $sql .= " and (p.ref like '%s') ";
      $queryParams[] = "%{$ref}%";
    }
    if ($supplierId) {
      $sql .= " and (p.supplier_id = '%s') ";
      $queryParams[] = $supplierId;
    }

    $sql .= " order by p.bizdt desc, p.ref desc
              limit %d, %d";
    $queryParams[] = $start;
    $queryParams[] = $limit;
    $data = $db->query($sql, $queryParams);
    foreach ($data as $v) {
      $result[] = [
        "id" => $v["id"],
        "ref" => $v["ref"],
        "billStatus" => $v["bill_status"] == 0 ? "待出库" : "已出库",
        "warehouseName" => $v["warehouse_name"],
        "supplierName" => $v["supplier_name"],
        "rejMoney" => $v["rejection_money"],
        "bizUserName" => $v["biz_user_name"],
        "inputUserName" => $v["input_user_name"],
        "bizDT" => $this->toYMD($v["bizdt"]),
        "dateCreated" => $v["date_created"],
        "receivingType" => $v["receiving_type"],
        "billMemo" => $v["bill_memo"],
        "tax" => $v["tax"],
        "rejMoneyWithTax" => $v["rejection_money_with_tax"],
        "pwRef" => $v["pw_ref"],
      ];
    }

    $sql = "select count(*) as cnt
            from t_pr_bill p, t_warehouse w, t_user u1, t_user u2, t_supplier s
            where (p.warehouse_id = w.id)
              and (p.biz_user_id = u1.id)
              and (p.input_user_id = u2.id)
              and (p.supplier_id = s.id) ";
    $queryParams = [];
    $ds = new DataOrgDAO($db);
    $rs = $ds->buildSQL(FIdConst::VAT_IN_REL_ADD, "p", $loginUserId);
    if ($rs) {
      $sql .= " and " . $rs[0];
      $queryParams = $rs[1];
    }

    if ($ref) {
      $sql .= " and (p.ref like '%s') ";
      $queryParams[] = "%{$ref}%";
    }
    if ($supplierId) {
      $sql .= " and (p.supplier_id = '%s') ";
      $queryParams[] = $supplierId;
    }

    $data = $db->query($sql, $queryParams);
    $cnt = $data[0]["cnt"];

    return [
      "dataList" => $result,
      "totalCount" => $cnt
    ];
  }

  /**
   * 采购退货出库单明细列表
   *
   * @param array $params
   * @return array
   */
  public function prBillDetailList($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    if ($this->companyIdNotExists($companyId)) {
      return $this->emptyResult();
    }

    $bcDAO = new BizConfigDAO($db);
    $dataScale = $bcDAO->getGoodsCountDecNumber($companyId);
    $fmt = "decimal(19, " . $dataScale . ")";

    // id：采购退货出库单id
    $id = $params["id"];

    $sql = "select g.code, g.name, g.spec, u.name as unit_name,
              convert(p.rejection_goods_count, $fmt) as rej_count, p.rejection_goods_price as rej_price,
              p.rejection_money as rej_money, p.memo, p.tax, p.tax_rate,
              p.rejection_money_with_tax, p.rejection_goods_price_with_tax
            from t_pr_bill_detail p, t_goods g, t_goods_unit u
            where p.goods_id = g.id and g.unit_id = u.id and p.prbill_id = '%s'
              and p.rejection_goods_count > 0
              order by p.show_order";
    $result = [];
    $data = $db->query($sql, $id);
    foreach ($data as $v) {
      $result[] = [
        "goodsCode" => $v["code"],
        "goodsName" => $v["name"],
        "goodsSpec" => $v["spec"],
        "unitName" => $v["unit_name"],
        "rejCount" => $v["rej_count"],
        "rejPrice" => $v["rej_price"],
        "rejMoney" => $v["rej_money"],
        "memo" => $v["memo"],
        "tax" => $v["tax"],
        "taxRate" => $v["tax_rate"],
        "rejPriceWithTax" => $v["rejection_goods_price_with_tax"],
        "rejMoneyWithTax" => $v["rejection_money_with_tax"]
      ];
    }

    return $result;
  }

  /**
   * 新建关联
   */
  public function addRelation(&$params)
  {
    $db = $this->db;

    $vatId = $params["vatId"];
    $billId = $params["billId"];
    $billType = $params["billType"];

    if (!in_array($billType, [1, 2, 3])) {
      return $this->badParam("billType");
    }

    // 检查发票记录是否存在
    $sql = "select ref, v_status
            from t_vat_income
            where id = '%s' ";
    $data = $db->query($sql, $vatId);
    if (!$data) {
      return $this->bad("要关联的发票记录不存在");
    }
    $vatRef = $data[0]["ref"];
    $status = $data[0]["v_status"];
    if ($status > 1000) {
      return $this->bad("发票记录(发票号：{$vatRef})已经核销，不能再新建单据关联");
    }

    // 检查单据是否存在
    $billRef = "";
    $billName = "";
    if ($billType == 1) {
      // 采购入库单
      $billName = "采购入库单";
      $sql = "select ref
              from t_pw_bill
              where id = '%s' ";
      $data = $db->query($sql, $billId);
      if (!$data) {
        return $this->bad("要关联的采购入库单不存在");
      }
      $billRef = $data[0]["ref"];
    } elseif ($billType == 2) {
      // 采购退货出库单
      $billName = "采购退货出库单";
      $sql = "select ref
              from t_pr_bill
              where id = '%s' ";
      $data = $db->query($sql, $billId);
      if (!$data) {
        return $this->bad("要关联的采购入库单不存在");
      }
      $billRef = $data[0]["ref"];
    } elseif ($billType == 3) {
      // 采购费用
      // TODO
    }

    // 检查是否已经关联过了
    // 一张单据可以和多张发票记录关联
    // 例如场景：采购的物料分三次入库，形成三张入库单，但是供应商只开出了两张发票
    //          至少有一张发票包含两张入库单里面的物料
    //          更乱的场景，是发票里面的物料和入库单里面的物料，需要人工识别才能分劈开
    // PSI中设计模型是：一张发票记录可以对应多条明细记录（明细可以入库/退货出库物料明细或采购费用明细）。

    // 一张入库单上的一个明细物料，如果是多次开票，怎么处理？
    // 例如，供应商的确是两张发票，两张送货单，这时候，供应商是正常开票业务
    // 但是是一次到货，并且我方库管合并一次录入了。
    // 再加上，发票是过了很久才到，这时候，用系统里面的退货再入库，也不好操作了
    // PSI的设计是：在t_vat_income_bill_detail中增加了一系列iv_字段来实现明细记录的分劈

    if ($billType == 1) {
      // 采购入库单
      $sql = "select count(*) as cnt
              from t_pw_bill_detail
              where id not in (
                  select detail_id as id
                  from t_vat_income_bill_detail
                  where bill_id = '%s' and bill_type = 1
                )
                and pwbill_id = '%s' ";
      $data = $db->query($sql, $billId, $billId);
      $cnt = $data[0]["cnt"];
      if ($cnt == 0) {
        return $this->bad("采购入库单(单号：{$billRef})所有的明细物料都已经关联发票记录了");
      }
    } elseif ($billType == 2) {
      // 采购退货出库单
      $sql = "select count(*) as cnt
              from t_pr_bill_detail
              where id not in (
                  select detail_id as id
                  from t_vat_income_bill_detail
                  where bill_id = '%s' and bill_type = 2
                )
                and prbill_id = '%s' ";
      $data = $db->query($sql, $billId, $billId);
      $cnt = $data[0]["cnt"];
      if ($cnt == 0) {
        return $this->bad("采购退货出库单(单号：{$billRef})所有的明细物料都已经关联发票记录了");
      }
    } elseif ($billType == 3) {
      // 采购费用
      // TODO
    }

    $id = $this->newId();
    $sql = "insert into t_vat_income_bill
            (id, vat_id, bill_id, bill_ref, bill_type) values
            ('%s', '%s', '%s', '%s', %d)";
    $rc = $db->execute($sql, $id, $vatId, $billId, $billRef, $billType);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }
    $params["id"] = $id;

    // 关联明细
    if ($billType == 1) {
      // 采购入库
      $sql = "select id
              from t_pw_bill_detail
              where id not in (
                  select detail_id as id
                  from t_vat_income_bill_detail
                  where bill_id = '%s' and bill_type = 1
                )
                and pwbill_id = '%s' 
              order by show_order";
      $data = $db->query($sql, $billId, $billId);
      foreach ($data as $v) {
        $detailId = $v["id"];
        $sql = "insert into t_vat_income_bill_detail(
                  id, vat_id, bill_id, bill_ref, bill_type, detail_id)
                values(
                  '%s', '%s', '%s', '%s', %d, '%s')";
        $rc = $db->execute($sql, $this->newId(), $vatId, $billId, $billRef, $billType, $detailId);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }
      }
    } elseif ($billType == 2) {
      // 采购退货出库
      $sql = "select id
              from t_pr_bill_detail
              where id not in (
                  select detail_id as id
                  from t_vat_income_bill_detail
                  where bill_id = '%s' and bill_type = 1
                )
                and prbill_id = '%s' 
              order by show_order";
      $data = $db->query($sql, $billId, $billId);
      foreach ($data as $v) {
        $detailId = $v["id"];
        $sql = "insert into t_vat_income_bill_detail(
                  id, vat_id, bill_id, bill_ref, bill_type, detail_id)
                values(
                  '%s', '%s', '%s', '%s', %d, '%s')";
        $rc = $db->execute($sql, $this->newId(), $vatId, $billId, $billRef, $billType, $detailId);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }
      }
    } elseif ($billType == 3) {
      // 采购费用
    }

    // 业务日志
    $log = "发票记录(发票号：{$vatRef})关联{$billName}(单号:{$billRef})";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 查询发票和单据关联列表
   */
  public function incomeBillList($params)
  {
    $db = $this->db;

    $id = $params["id"];

    $sql = "select id, bill_type, bill_id
            from t_vat_income_bill
            where vat_id = '%s'
            order by bill_ref";
    $data = $db->query($sql, $id);
    $result = [];
    foreach ($data as $v) {
      $billId = $v["bill_id"];
      $billType = $v["bill_type"];
      $billRef = "";
      $goodsMoney = 0;
      if ($billType == 1) {
        $sql = "select ref, goods_money from t_pw_bill where id = '%s' ";
        $dataBill = $db->query($sql, $billId);
        if ($dataBill) {
          $billRef = $dataBill[0]["ref"];
          $goodsMoney = $dataBill[0]["goods_money"];
        }
      } else if ($billType == 2) {
        $sql = "select ref, rejection_money from t_pr_bill where id = '%s' ";
        $dataBill = $db->query($sql, $billId);
        if ($dataBill) {
          $billRef = $dataBill[0]["ref"];
          // 退货：金额用负数，（在单据中存储的是正数，现在想来似乎是当时的设计失误）
          $goodsMoney = -$dataBill[0]["rejection_money"];
        }
      } else {
        // 莫名的bug
        continue;
      }

      $result[] = [
        "id" => $v["id"],
        "billRef" => $billRef,
        "billType" => $this->billTypeCodeToName($billType),
        "goodsMoney" => $goodsMoney,
      ];
    }

    return $result;
  }

  private function billTypeCodeToName($code)
  {
    switch ($code) {
      case 1:
        return "采购入库单";
      case 2:
        return "采购退货出库单";
      default:
        return "[无效的编码]";
    }
  }

  /**
   * 删除关联
   */
  public function deleteRelation(&$params)
  {
    $db = $this->db;

    // 发票和单据的关联id
    $id = $params["id"];

    $sql = "select vat_id, bill_ref, bill_type
            from t_vat_income_bill
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      // 莫名的bug
      return $this->bad("要删除的关联不存在");
    }
    $vatId = $data[0]["vat_id"];
    $billName = $this->billTypeCodeToName($data[0]["bill_type"]);
    $billRef = $data[0]["bill_ref"];

    // 检查发票记录状态
    $sql = "select v_status, ref
            from t_vat_income
            where id = '%s' ";
    $data = $db->query($sql, $vatId);
    if (!$data) {
      // 莫名的bug
      return $this->bad("发票记录不存在");
    }
    $status = $data[0]["v_status"];
    $vatRef = $data[0]["ref"];
    if ($status > 1000) {
      return $this->bad("发票记录已经核销，不能再删除票据关联");
    }

    $sql = "delete from t_vat_income_bill where id = '%s' ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "删除发票(发票号：{$vatRef})和{$billName}(单号：{$billRef})的关联";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 核销
   */
  public function commitVatIncome(&$params)
  {
    $db = $this->db;

    // 发票记录id
    $id = $params["id"];

    $sql = "select ref, v_status, v_dt, goods_money, tax, v_category, company_id
            from t_vat_income
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要核销的发票记录不存在");
    }
    $v = $data[0];
    $vatStatus = $v["v_status"];
    $vatRef = $v["ref"];
    $vatGoodsMoney = $v["goods_money"];
    $vatTax = $v["tax"];
    $vatCategory = $v["v_category"];
    $companyId = $v["company_id"];

    if ($vatStatus > 1000) {
      return $this->bad("发票记录(发票号：{$vatRef})已经核销过了，不能重复核销");
    }

    if (!in_array($vatCategory, [1, 2, 3])) {
      // 莫名的bug
      return $this->badParam("vatCategroy");
    }

    // 检查货款是否一致
    $sql = "select bill_id, bill_type
            from t_vat_income_bill
            where vat_id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("发票记录(发票号：{$vatRef})还没有关联业务单据，不能做核销处理");
    }
    $sum = 0;
    foreach ($data as $v) {
      $billId = $v["bill_id"];
      $billType = $v["bill_type"];
      if ($billType == 1) {
        // 采购入库
        $sql = "select goods_money from t_pw_bill where id = '%s' ";
        $dataBill = $db->query($sql, $billId);
        if ($dataBill) {
          $sum += $dataBill[0]["goods_money"];
        }
      } elseif ($billType == 2) {
        // 采购退货出库
        $sql = "select rejection_money from t_pr_bill where id = '%s' ";
        $dataBill = $db->query($sql, $billId);
        if ($dataBill) {
          $sum += (-$dataBill[0]["rejection_money"]);
        }
      } else {
        // bug
        continue;
      }
    }
    if (abs($vatGoodsMoney - $sum) > 0.0000001) {
      return $this->bad("发票记录中的货款({$vatGoodsMoney})和单据中的货款合计({$sum})不相等，不能进行核销操作");
    }

    // 业务日志
    $sql = "select name from t_org where id = '%s'";
    $data = $db->query($sql, $companyId);
    $companyName = $data[0]["name"];
    $log = "{$companyName} - 核销发票记录，发票号：{$vatRef}";
    $params["log"] = $log;

    // 可以抵扣销项的发票: 专票和某些特殊可抵扣的普票
    if ($vatCategory == 1 || $vatCategory == 3) {
      // 可抵扣的业务场景下，税款不能进存货成本，只需要把发票记录标记为已完成核销即可
      $sql = "update t_vat_income
              set v_status = 3000
              where id = '%s' ";
      $rc = $db->execute($sql, $id);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      // 操作成功
      return null;
    }

    // 下面的业务逻辑是处理 - 不可以抵扣的：常见的普票，把税款转入存货成本
    // 目前的实现采用简单算法，把税款转入第一张采购入库单中的第一个物料上
    // TODO 如果这种简单分摊算法不满足实际需求，需要自行做二次开发
    // TODO 如果采购入库是自动拆分的，需要把成本转入到最终拆分的物料上


    // 如果关联了多张采购入库单，需要保证采购入库单的入库仓库是同一个
    // 这个限制只是为了让税款转存货成本写法简单些，如果不满足实际业务需求，就可以自行二次开发
    $sql = "select bill_id
            from t_vat_income_bill
            where vat_id = '%s' and bill_type = 1";
    $dataBillList = $db->query($sql, $id);
    if (count($dataBillList) == 0) {
      return $this->bad("没有关联采购入库单，无法完成核销操作");
    }
    if (count($dataBillList) > 1) {
      $warehouseIdList = [];
      foreach ($dataBillList as $t) {
        $billId = $t["bill_id"];
        $sql = "select warehouse_id from t_pw_bill where id = '%s' ";
        $data = $db->query($sql, $billId);
        $warehouseId = $data[0]["warehouse_id"];
        if (!in_array($warehouseId, $warehouseIdList)) {
          $warehouseIdList[] = $warehouseId;
        }
      }
      if (count($warehouseIdList) > 1) {
        return $this->bad("关联的采购入库单的入库仓部不是同一个仓库，目前不支持这种场景下的核销");
      }
    }

    // 如果关联的采购入库单还没有提交，这是在途状态，这时候不能做核销
    foreach ($dataBillList as $t) {
      $billId = $t["bill_id"];
      $sql = "select bill_status, ref from t_pw_bill where id = '%s' ";
      $data = $db->query($sql, $billId);
      $status = $data[0]["bill_status"];
      if ($status == 0) {
        $ref = $data[0]["ref"];
        return $this->bad("采购入库单(单号：{$ref})还处于在途状态，此时不能做发票核销操作");
      }
    }

    // 把税款结转到第一张采购入库单上的第一个物料上
    $billId = $dataBillList[0];
    $sql = "select warehouse_id from t_pw_bill where id = '%s' ";
    $data = $db->query($sql, $billId);
    $warehouseId = $data[0]["warehouse_id"];
    $sql = "select goods_id from t_pw_bill_detail where pwbill_id = '%s' order by show_order limit 1";
    $data = $db->query($sql, $billId);
    $goodsId = $data[0]["goods_id"];
    // 查询库存账中的存货的数量和金额
    $sql = "select balance_count, balance_money
            from t_inventory
            where warehouse_id = '%s' and goods_id = '%s' ";
    $data = $db->query($sql, $warehouseId, $goodsId);
    $balanceCount = 0;
    $balanceMoney = 0;
    if (!$data) {
      // 莫名的bug
      // do nothing
    }
    $balanceCount = $data[0]["balance_count"];
    $balanceMoney = $data[0]["balance_money"];
    $balanceMoney += $vatTax;

    // 创建盘点单
    $dao = new ICBillDAO($db);
    $items = [];
    $items[] = [
      "goodsId" => $goodsId,
      "goodsCount" => $balanceCount,
      "goodsMoney" => $balanceMoney,
    ];
    $bill = [
      "bizDT" => date("Y-m-d"),
      "warehouseId" => $warehouseId,
      "bizUserId" => $params["loginUserId"],
      "loginUserId" => $params["loginUserId"],
      "dataOrg" => $params["dataOrg"],
      "companyId" => $companyId,
      "billMemo" => "核销发票（发票号：{$vatRef}），系统自动生成，把税款结转到存货成本",
      "items" => $items,
    ];

    $rc = $dao->addICBill($bill);
    if ($rc) {
      return $rc;
    }

    // 提交盘点单
    $icbillId = $bill["id"];
    $icbillParams = [
      "id" => $icbillId,
      "companyId" => $companyId,
    ];
    $rc = $dao->commitICBill($icbillParams);
    if ($rc) {
      return $rc;
    }

    // 操作成功
    return null;
  }
}
